﻿using Dapper;
using System;
using BCEntity.Website.UserAddress;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using BCCommon;

namespace BCData.Website.UserAddress
{
    /// <summary>
    /// 用户地址
    /// </summary>
    public class UserAddressData : IUserAddressData
    {
        private readonly IDatabaseContext databaseContext;
        private const string QUERY_EXISTS = @"SELECT 1 FROM useraddress";
        private const string QUERY = @"SELECT UserAddressId,CountyCode,CountyName,Consignee,ConsigneePhone,Address,IsDefault,CreateTime,UserId,ProvinceId,ProvinceCode,ProvinceName,CityId,CityCode,CityName,CountyId FROM useraddress";
        public UserAddressData(IDatabaseContext databaseContext)
        {
            this.databaseContext = databaseContext;
        }
        #region 检查用户地址是否存在
        public async Task<bool> Exists(long userAddressId)
        {
            string sql = string.Format(@"{0} WHERE UserAddressId=?UserAddressId", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserAddressId", userAddressId, DbType.Int64);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }
        #endregion

        #region 检查用户地址是否存在
        public async Task<bool> ExistsByUserId(long userId)
        {
            string sql = string.Format(@"{0} WHERE UserId=?UserId", QUERY_EXISTS);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserId", userId, DbType.Int64);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }
        #endregion

        #region 添加用户地址
        public async Task<UserAddressEntity> Add(UserAddressEntity entity)
        {
            string sql = SqlCommands.GetInsertSql("useraddress", new string[] { "Consignee", "ConsigneePhone", "Address", "IsDefault", "UserId", "ProvinceId", "ProvinceCode", "ProvinceName", "CityId", "CityCode", "CityName", });
            sql = string.Format("{0} {1} WHERE UserAddressId=LAST_INSERT_ID();", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Consignee", entity.Consignee?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?ConsigneePhone", entity.ConsigneePhone?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?Address", entity.Address?.Trim(), DbType.AnsiString, size: 100);
            parameters.Add("?IsDefault", entity.IsDefault, DbType.Boolean);
            parameters.Add("?UserId", entity.UserId, DbType.Int64, size: 20);
            parameters.Add("?ProvinceId", entity.ProvinceId, DbType.Int64, size: 20);
            parameters.Add("?ProvinceCode", entity.ProvinceCode?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?ProvinceName", entity.ProvinceName?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?CityId", entity.CityId, DbType.Int64, size: 20);
            parameters.Add("?CityCode", entity.CityCode?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?CityName", entity.CityName?.Trim(), DbType.AnsiString, size: 50);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<UserAddressEntity>(sql, parameters, databaseContext.Transaction);

        }
        #endregion

        #region 修改用户地址
        public async Task<UserAddressEntity> Update(UserAddressEntity entity)
        {
            string sql = SqlCommands.GetUpdateSql("useraddress", new string[] { "CountyCode", "CountyName", "Consignee", "ConsigneePhone", "Address", "IsDefault", "UserId", "ProvinceId", "ProvinceCode", "ProvinceName", "CityId", "CityCode", "CityName", "CountyId" }, new string[] { "UserAddressId" });
            sql = string.Format("{0} {1} WHERE UserAddressId=?UserAddressId;", sql, QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserAddressId", entity.UserAddressId, DbType.Int64, size: 20);
            parameters.Add("?CountyCode", entity.CountyCode?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?CountyName", entity.CountyName?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?Consignee", entity.Consignee?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?ConsigneePhone", entity.ConsigneePhone?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?Address", entity.Address?.Trim(), DbType.AnsiString, size: 100);
            parameters.Add("?IsDefault", entity.IsDefault, DbType.Boolean);
            parameters.Add("?UserId", entity.UserId, DbType.Int64, size: 20);
            parameters.Add("?ProvinceId", entity.ProvinceId, DbType.Int64, size: 20);
            parameters.Add("?ProvinceCode", entity.ProvinceCode?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?ProvinceName", entity.ProvinceName?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?CityId", entity.CityId, DbType.Int64, size: 20);
            parameters.Add("?CityCode", entity.CityCode?.Trim(), DbType.AnsiString, size: 20);
            parameters.Add("?CityName", entity.CityName?.Trim(), DbType.AnsiString, size: 50);
            parameters.Add("?CountyId", entity.CountyId, DbType.Int64, size: 20);
            return await this.databaseContext.Master.QueryFirstOrDefaultAsync<UserAddressEntity>(sql, parameters, databaseContext.Transaction);
        }
        #endregion

        #region 取消默认地址
        public async Task<bool> CancelIsDefault(long userId)
        {
            string sql = SqlCommands.GetUpdateSql("useraddress", new string[] { "IsDefault" }, new string[] { "UserId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?IsDefault", false, DbType.Boolean);
            parameters.Add("?UserId", userId, DbType.Int64, size: 20);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }
        #endregion

        #region 取消默认地址
        public async Task<bool> SettingIsDefault(long userAddressId)
        {
            string sql = SqlCommands.GetUpdateSql("useraddress", new string[] { "IsDefault" }, new string[] { "UserAddressId" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?IsDefault", true, DbType.Boolean);
            parameters.Add("?UserAddressId", userAddressId, DbType.Int64, size: 20);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }
        #endregion

        #region 删除用户地址
        public async Task<bool> Delete(long userAddressId)
        {
            string sql = @"DELETE FROM useraddress WHERE UserAddressId=?UserAddressId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserAddressId", userAddressId, DbType.Int64);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }
        #endregion

        #region 获取用户地址
        public async Task<UserAddressEntity> GetEntity(long userAddressId)
        {
            string sql = string.Format(@"{0} WHERE UserAddressId=?UserAddressId", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserAddressId", userAddressId, DbType.Int64);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<UserAddressEntity>(sql, parameters);
        }
        #endregion

        #region 获取用户地址总数
        public async Task<int> Count(long userId)
        {
            string sql = @"SELECT COUNT(1) FROM useraddress WHERE UserId=?UserId";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserId", userId, DbType.Int64);
            return await this.databaseContext.Slave.ExecuteScalarAsync<int>(sql, parameters);
        }
        #endregion

        #region 获取用户地址列表
        public async Task<IEnumerable<UserAddressEntity>> GetList(long userId)
        {
            string sql = string.Format(@"{0} WHERE UserId=?UserId", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?UserId", userId, DbType.Int64);
            return await this.databaseContext.Slave.QueryAsync<UserAddressEntity>(sql, parameters);
        }
        #endregion

        #region 获取用户地址分页
        public async Task<(IEnumerable<UserAddressEntity>, int)> Query(long? userId, DateTime? start, DateTime? end, string sortName, bool? ascending, int pageIndex, int pageSize)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (userId.HasValue)
            {
                wheres.Add("UserId=?UserId");
                parameters.Add("?UserId", userId.Value, DbType.Int64);
            }
            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("CreateTime>=?Start");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime<=?End");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("CreateTime>=?Start AND CreateTime<=?End");
                parameters.Add("?Start", start.Value, DbType.DateTime);
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            string sql = string.Format(@"{0} {1} ORDER BY {4} {5} LIMIT {2},{3}; SELECT COUNT(1) FROM useraddress {1}",
                  QUERY,
                  wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                  (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                  pageSize,
                  string.IsNullOrWhiteSpace(sortName) ? "CreateTime" : sortName,
                  ascending.HasValue && ascending.Value ? "ASC" : "DESC");
            return await this.databaseContext.Slave.QueryMultipleAsync<UserAddressEntity, int>(sql, parameters);
        }
        #endregion
    }
}



